package Oracle;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.io.RandomAccessFile;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class GetFileDownload {
	private static Connection con = null;

	public GetFileDownload() {
		if (con == null) {
			con = getConnection();
		}
	}

	public static void appendLog(String fileName, String content) {
		try {
			content = new String(content.getBytes("GBK"), "ISO-8859-1");
			RandomAccessFile randomFile = new RandomAccessFile(fileName, "rw");
			long fileLength = randomFile.length();
			randomFile.seek(fileLength);
			randomFile.writeBytes(content + "\r\n");
			randomFile.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@ (DESCRIPTION = (ADDRESS = (PROTOCOL =TCP)"
			+ "(HOST = 10.1.7.9)(PORT = 1521)) (LOAD_BALANCE = yes)(CONNECT_DATA = (SERVICE_NAME = tc) ) )";
	private static final String USERNAME = "dataexchange";
	private static final String PASSWORD = "dataexchange123";

	private static Connection getConnection() {
		try {
			Class.forName(DRIVER);
			if (con == null) {
				con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
				con.setAutoCommit(false);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}

	public static void main(String[] args) throws SQLException {
		GetFileDownload g = new GetFileDownload();
		g.prtIsEmail();
		// g.selectCHYIII_DEC_TDOWNLOADDATALIST("125017");
	}

	private static boolean match(String regex, String str) {
		Pattern pattern = Pattern.compile(regex);
		Matcher matcher = pattern.matcher(str);
		return matcher.matches();
	}

	public static boolean isEmail(String str) {
		// #################################################33
		//String regex = "^\\s*\\w+(?:\\.{0,1}[\\w-]+)*@[a-zA-Z0-9]+(?:[-.][a-zA-Z0-9]+)*\\.[a-zA-Z]+\\s*$";
		String regex = "^\\s*\\w+(?:\\.{0,1}[\\w-]+)*@[a-zA-Z0-9]+(?:[-.][a-zA-Z0-9]+)*\\.[a-zA-Z]+\\s*$";
		//String regex = "^([\\w-\\.]+)@((\\[[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.)|(([\\w-]+\\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\\]?)$";
		// shigongyun@@mychery.com 不是邮箱
		// Liulili@my chery.com 不是邮箱
		// b@.test.com 不是邮箱
		// yupeng.xie@cnlaunch.com <yupeng.xie@cnlaunch.com> 不是邮箱
		// 13776639888<13776639888@139.com> 不是邮箱
		// zhang.qiujuan@norxg.com <zhang.qiujuan@norxg.com> 不是邮箱
		// r&d.dzh@dnlzhmagna.com 不是邮箱
		// tianlijun@hengyao@net.cn 不是邮箱
		// Yisheng_xia@bonaire.cn <Yisheng_xia@bonaire.cn> 不是邮箱
		// zxjxxs(quxiao)@163.com 不是邮箱
		// 多余的联系人 不是邮箱
		// Jerry Li@dayco.com 不是邮箱
		// ######################################333
		//String regex = "^([\\w-\\.]+)@((\\[[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.)|(([\\w-]+\\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\\]?)$";
		
//		shigongyun@@mychery.com   不是邮箱
//		Liulili@my chery.com   不是邮箱
//		b@.test.com   不是邮箱
//		yupeng.xie@cnlaunch.com <yupeng.xie@cnlaunch.com>   不是邮箱
//		13776639888<13776639888@139.com>   不是邮箱
//		zhang.qiujuan@norxg.com <zhang.qiujuan@norxg.com>   不是邮箱
//		r&d.dzh@dnlzhmagna.com   不是邮箱
//		 kittyzhao1@163.com   不是邮箱
//		tianlijun@hengyao@net.cn   不是邮箱
//		Yisheng_xia@bonaire.cn <Yisheng_xia@bonaire.cn>   不是邮箱
//		zxjxxs(quxiao)@163.com   不是邮箱
//		多余的联系人   不是邮箱
//		Jerry Li@dayco.com   不是邮箱
		// #########################################################
		return match(regex, str);
	}

	public void prtIsEmail() {

		ResultSet rs;
		String email;
		try {
			String sql = "SELECT SCEMAIL,SUPPLIERCODE,DEPTCODE,SCNAME FROM  CHYIII_DEC_TSUPPLIERCONTACT";
			PreparedStatement pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			email = "";
			while (rs.next()) {
				email = rs.getString(1);
				if (!isEmail(email)) {
					System.out.println("SUPPLIERCODE = "+rs.getString(2)+"   DEPTCODE =   "+rs.getString(3)+"     姓名=  "+rs.getString(4)+"    的邮箱："+email + "   不是邮箱");
				}
//				if (!CheckEmailUtils.isEmails(email)) {
//					System.out.println("SUPPLIERCODE = "+rs.getString(2)+"   DEPTCODE =   "+rs.getString(3)+"     姓名=  "+rs.getString(4)+"    的邮箱："+email + "   不是邮箱");
//				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void readFileByLines(String fileName) {
		File file = new File(fileName);
		BufferedReader reader = null;
		try {
			reader = new BufferedReader(new FileReader(file));
			String tempString = null;
			int line = 1;
			while ((tempString = reader.readLine()) != null) {
				try {
					String arr[] = selectCHYIII_DEC_TDOWNLOADTASK(tempString
							.trim());
					String TASKCOUNT = selectCHYIII_DEC_TDOWNLOADDATALIST(tempString
							.trim());
					if (arr != null) {
						String ID = arr[0];
						String FILECOUNT = arr[1];
						String STATE = arr[2];
						if (STATE.equals("3")) {
							if (!FILECOUNT.equals(TASKCOUNT)) {
								appendLog("D:\\rrrrrrrrrrrrrrrrrrrr不相等的统计.txt",
										"ID  =  " + ID + "  下载数量  =  "
												+ FILECOUNT + "  状态  =  "
												+ STATE + "  文件实际数量  =  "
												+ TASKCOUNT);
							} else {
								appendLog("D:\\ccccccccccccccccccccc相等的统计.txt",
										"ID  =  " + ID + "  下载数量  =  "
												+ FILECOUNT + "  状态  =  "
												+ STATE + "  文件实际数量  =  "
												+ TASKCOUNT);
							}
						}
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}

				// tempString;
			}
			reader.close();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (reader != null) {
				try {
					reader.close();
				} catch (IOException e1) {
				}
			}
		}
	}

	public String[] selectCHYIII_DEC_TDOWNLOADTASK(String ID)
			throws SQLException {
		String sql = "SELECT  ID ,FILEDOWNLOADEDCOUNT,STATE FROM  CHYIII_DEC_TDOWNLOADTASK  WHERE  ID ='"
				+ ID + "'";
		PreparedStatement pst = con.prepareStatement(sql);
		ResultSet rs = pst.executeQuery();
		String arr[] = new String[3];

		while (rs.next()) {
			arr[0] = rs.getString(1);
			arr[1] = rs.getString(2);
			arr[2] = rs.getString(3);
			// System.out.println("ID=" + rs.getString(1));
			// System.out.println("FILECOUNT=" + rs.getString(2));
			// System.out.println("STATE=" + rs.getString(3));
		}
		// con.commit();
		// pst.close();
		// con.close();
		return arr;
	}

	public String selectCHYIII_DEC_TDOWNLOADDATALIST(String TASKID)
			throws SQLException {
		String sql = "SELECT  COUNT ( ID ) FROM  CHYIII_DEC_TDOWNLOADDATALIST WHERE TASKID ='"
				+ TASKID + "'";
		PreparedStatement pst = con.prepareStatement(sql);
		ResultSet rs = pst.executeQuery();
		String count = "";
		while (rs.next()) {
			count = rs.getString(1);
		}
		// con.commit();
		// pst.close();
		// con.close();
		return count;
	}
}
